package com.study.spark.scala.sql

import org.apache.spark.sql.SparkSession

/**
 * SQL 时间处理函数
 *
 * @author stephen
 * @date 2019-09-27 18:10
 */
object TimeFunctionDemo {

  def main(args: Array[String]): Unit = {
    val spark = SparkSession
      .builder()
      .appName("TimeFunctionDemo")
      .master("local[*]")
      .getOrCreate()

    // 隐式转换
    import spark.implicits._
    import org.apache.spark.sql.functions._

    val df = spark.sql("select current_date() as dt, current_timestamp() as ts, '2019-01-01 04:00:00' as tm_str")
    df.show(false)

    // dayofyear：一年中的第几天
    df.select(dayofyear($"dt"), dayofyear($"ts"), dayofyear($"tm_str")).show()
    // 小时
    df.select(hour($"dt"), hour($"ts"), hour($"tm_str")).show()
    // 加减、格式转换操作
    df.select(
      // 两个日志天数之差
      datediff(add_months($"dt", 2), add_months($"ts", 1)).as("diff_days"),
      // 日期格式化
      date_format($"ts", "HH:mm:SS"),
      date_format($"tm_str", "MM/dd/YYYY"),
      // 日期转成时间戳
      unix_timestamp($"dt","YYYY-MM-dd")
    ).show()


    spark.stop()
  }
}
